/*
 * Copyright (C) 2012 TopCoder Inc., All Rights Reserved.
 */
package com.topcoder.utilities.dwload;

import com.topcoder.shared.util.DBMS;
import com.topcoder.shared.util.sql.DBUtility;

import java.text.DateFormat;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Time;
import java.sql.Date;
import java.util.Calendar;
import java.util.Locale;
import java.text.SimpleDateFormat;

import sun.util.logging.resources.logging;

/**
 * <p>A DB utility which is intended to load data for <code>Jira Issue</code> from bugs_oltp 
 * database to warehouse database.</p>
 *
 * <p>Below is the sample configuration for this tool:</p>
 *
 * <p>jiraDataLoadingToolMysql.xml:</p>
 * <pre>
 * &lt;jiraDataLoadingTool&gt
 *     &lt;sourcesList&gt
 *         &lt;source name="JiraData"
 *                 value="jdbc:mysql://DBHOST:DBPORT/bugs_oltp?user=bugs&amp;password=Aa3E6aKZ"/&gt
 *     &lt;/sourcesList&gt
 *     &lt;parameterList&gt
 *     &lt;/parameterList&gt
 * &lt;/jiraDataLoadingTool&gt
 * </pre>
 * 
 * <p>jiraDataLoadingTool.xml:</p>
 * <pre>
 * &lt;jiraDataLoadingTool&gt;
 *      &lt;sourcesList&gt;
 *          &lt;source name="WarehouseData"
 *                  value="jdbc:informix-sqli://DBHOST:DBPORT/tcs_dw:INFORMIXSERVER=informixoltp_tcp;
 *                         user=DBUSERNAME;password=DBPASSWORD"/&gt;
 *      &lt;/sourcesList&gt;
 *      &lt;parameterList&gt;
 *      &lt;/parameterList&gt;
 * &lt;/jiraDataLoadingTool&gt;
 * </pre>
 * 
 * @author leonzhou
 * @version 1.0 (Module Assembly - Pull Data from TC jira to dw)
 */
public class JiraDataLoadUtility extends DBUtility {

    /**
     * <p>A <code>String</code> providing the name for pre-configured data source to be used for obtaining connections
     * to jira database.</p>
     */
    private static final String JIRA_DATA_SOURCE = "JiraData";

    /**
     * <p>A <code>String</code> providing the name for pre-configured data source to be used for obtaining connections
     * to warehouse database.</p>
     */
    private static final String WAREHOUSE_DATA_SOURCE = "WarehouseData";

    /**
     * <p>An <code>int</code> providing the number of rows with transactional data that should be fetched from the 
     * database when more rows are needed.</p>
     */
    private static final int DATA_FETCH_SIZE = 20;

    /**
     * <p>A <code>String</code> providing the base ptah for file with database source configuration for obtaining connections to 
     * jira database</p>
     */
    private static final String JIRA_ISSUE_DATA_SOURCE_FILE 
        = "jiraDataLoadingToolMysql.xml";
    
    /**
     * <p>A <code>String</code> providing the base ptah for files with SQL statements for selecting the records to be
     * loaded from jira database to warehouse database.</p>
     */
    private static final String JIRA_ISSUE_SQL_FILES_BASE 
        = "/home/tc/web/scripts/sql";
    
    /**
     * <p>A <code>String</code> providing the SQL statement to be used for selecting records for loading to 
     * <code>tcs_dw:jira_issue</code> database table.</p>
     */
    private static final String JIRA_ISSUE_LOAD_SQL_FILE 
        = JIRA_ISSUE_SQL_FILES_BASE + "/load_jira_issue.sql";

    /**
     * <p>A <code>String</code> providing the SQL statement to be used for inserting records to 
     * <code>tcs_dw:jira_issue</code> database table.</p>
     */
    private static final String JIRA_ISSUE_INSERT_SQL 
        = "INSERT INTO jira_issue (ticket_id, reporter, assignee, summary, description, created, updated, duedate, " +
        		"resolutiondate, votes, winner, payment_amount, contest_id) " +
        		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    /**
     * <p>A <code>String</code> providing the SQL statement to be used for deleting records from 
     * <code>tcs_dw:jira_issue</code> database table.</p>
     */
    private static final String JIRA_ISSUE_DELETE_SQL = "DELETE FROM jira_issue";
    
    /**
     * <p>A <code>Connection</code> providing the connection to jira database</p>
     */
    private Connection sourceConn = null;
    
    /**
     * <p>A <code>Connection</code> providing the connection to warehouse database</p>
     */
	private Connection targetConn = null;
    
	private int JIRA_LOG_TYPE = 6;
    
	protected java.sql.Timestamp fStartTime = null;
    
    /**
     * <p>Constructs new <code>JiraDataLoadUtility</code> instance. This implementation does
     * nothing.</p>
     */
    public JiraDataLoadUtility() {
    }

    /**
     * <p>Runs this utility. Reads contents of SQL statements for selecting jira data from files and loads 
     * selected data into warehouse database tables.</p>
     */
    @Override
    protected void runUtility() {
        log.info("Jira data loading has started");

        fStartTime = new java.sql.Timestamp(System.currentTimeMillis());
        
        try {
			// Load data for jira_issue
            loadWarehouseData(JIRA_ISSUE_LOAD_SQL_FILE, "jira issue",
                              JIRA_ISSUE_DELETE_SQL,
                              JIRA_ISSUE_INSERT_SQL, new JiraDataStatementInitializer()); 

            
            setLastLoadDate();
            
        } catch (SQLException e) {
            log.error(e.getMessage());
            DBMS.printSqlException(true, e);
        } catch (IOException e) {
            log.error(e.getMessage());
            DBMS.printException(e);
        } catch (Exception e) {
			log.error(e.getMessage());
			DBMS.printException(e);
		} finally {
            log.info("Jira data loading has finished");
        }
    }

    /**
     * Show usage of the DBUtility. <p/> Subclasses should implement this method to show how the final user should call
     * them.
     *
     * @param msg The error message.
     */
    @Override
    protected void setUsageError(String msg) {
        sErrorMsg.setLength(0);
        sErrorMsg.append(msg).append("\n");
        sErrorMsg.append("JiraDataLoadUtility:\n");
        sErrorMsg.append("   The following parameters should be included in the XML or the command line\n");
        sErrorMsg.append("   -sourcesList : Configuration for data sources referencing the bugs_oltp and tcs_dw databases with ");
        sErrorMsg.append("data to be loaded from and to\n");
        fatal_error();
    }

    /**
     * <p>Loads teh content of SQL statement from the specified file.</p>
     * 
     * @param fileName a <code>String</code> providing the path to the target file.
     * @return a <code>String</code> providing the content of the file.
     * @throws IOException if an I/O error occurs while reading content of file.
     */
    private static String loadSQLFromFile(String fileName) throws IOException {
        BufferedReader reader = new BufferedReader(new FileReader(fileName));
        String line;
        StringBuilder sql = new StringBuilder();
        try {
            while ((line = reader.readLine()) != null) {
                sql.append(line).append(" ");
            }
        } finally {
            reader.close();
        }

        return sql.toString();
    }

    /**
     * <p>Performs the loading of jira data of specified type into respective warehouse database table.</p>
     *
     * @param loadSqlFileName            a <code>String</code> providing the path to file with SQL for selecting the
     *                                   data for loading.
     * @param dataType                   a <code>String</code> providing the description of data to be loaded.
     * @param deleteSQL                  a <code>String</code> providing the SQL statement for deleting the records from
     *                                   warehouse database table.
     * @param insertSQL                  a <code>String</code> providing the SQL statement for inserting records into
     *                                   warehouse database table.
     * @param insertStatementInitializer a <code>InsertStatementInitializer</code> to be used for initializing insert
     *                                   statements based on data loaded from the transactional database table.
     * @throws SQLException if an unexpected error occurs.
     * @throws IOException if an I/O error occurs while reading content of file.
     */
    private void loadWarehouseData(String loadSqlFileName, String dataType, String deleteSQL, String insertSQL,
                                   InsertStatementInitializer insertStatementInitializer)
        throws SQLException, IOException {
        log.info("Jira data loading for " + dataType + " has started");
        
        ResultSet result = null;

		PreparedStatement selectStatement = null;
        PreparedStatement deleteStatement = null;
        PreparedStatement insertStatement = null;
        try {
            // Retrieve all records from jira database table
            String selectSQL = loadSQLFromFile(loadSqlFileName);
            
            parseXML(JIRA_ISSUE_DATA_SOURCE_FILE);
            
			sourceConn = getMysqlDBConnection(JIRA_DATA_SOURCE);
			
			log.info("Running SQL statement: " + selectSQL);
			
			selectStatement = prepareStatement(selectSQL, sourceConn);
            selectStatement.setFetchSize(DATA_FETCH_SIZE);
			
            result = selectStatement.executeQuery();
            log.info("Retrieved jira records for " + dataType + " from database");

			targetConn = getInformixDBConnection(WAREHOUSE_DATA_SOURCE);
			
            // Delete all existing records from related warehouse database table
            deleteStatement = prepareStatement(deleteSQL, targetConn);
            log.info("Running SQL statement: " + deleteSQL);
            deleteStatement.executeUpdate();
            DBMS.close(deleteStatement);
            log.info("Removed records for " + dataType + " from warehouse database");

            // Prepare the statement for inserting records into 
            log.info("Running multiple SQL statements: " + insertSQL);
            insertStatement = prepareStatement(insertSQL, targetConn);

            // Iterate over all records from transactional database and insert respective record into warehouse database
            // table
            while (result.next()) {
                try {
                    log.info(insertStatementInitializer.printData(result));
                    insertStatementInitializer.initialize(result, insertStatement);
                    int insertedRecordsCount = insertStatement.executeUpdate();
                    log.info("test");
                    if (insertedRecordsCount != 1) {
                    	log.info("Wrong number of records inserted (expected 1): " + insertedRecordsCount);
                        throw new SQLException("Wrong number of records inserted (expected 1): " + insertedRecordsCount);
                    }
                } catch (SQLException e) {
                    log.error("Failed to insert record for " + dataType, e);
                }
            }
        } finally {
			
            DBMS.close(deleteStatement);
            DBMS.close(insertStatement);
            DBMS.close(selectStatement, result);
			if (sourceConn != null) 
			{ sourceConn.close(); }
            log.info("Jira data loading for " + dataType + " has finished");
        }
    }
	
	private Connection getInformixDBConnection(String source) throws SQLException {
	
		try {
			Class.forName("com.informix.jdbc.IfxDriver");
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage());
			throw new SQLException(e.getMessage());
		}
		Connection connection = DriverManager.getConnection((String)sources.get(source));
			
		return connection;
	}
	
	private Connection getMysqlDBConnection(String source) throws SQLException {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage());
			throw new SQLException(e.getMessage());
		}
		Connection connection = DriverManager.getConnection((String)sources.get(source));
	
		return connection;
	}
		
	/**
     * Call this method to create a PreparedStatement for a given sql.
     *
     * @param source The reference to target database.
     * @param sqlStr The sql query.
     * @return a statement to be executed for specified query.
     * @throws SQLException if an SQL error occurs while communicating to database.
     */
    private PreparedStatement prepareStatement(String sqlStr, Connection conn) throws SQLException {
       
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sqlStr);
        } catch (SQLException sqle) {
            throw sqle;
        }
        return ps;
    }

    /**
     * <p>An interface for initializer of the INSERT SQL statements based on the data provided by the current row from
     * selected result set.</p>
     */
    private static interface InsertStatementInitializer {

        /**
         * <p>Initializes the parameters for specified insert statement with data taken from the current row of 
         * specified result set.</p>
         * 
         * @param selectedData a <code>ResultSet</code> providing the selected data. 
         * @param insertStatement a <code>PreparedStatement</code> providing the SQL statement for inserting record.
         * @throws SQLException if an SQL error occurs while retrieving data for specified record.
         */
        void initialize(ResultSet selectedData, PreparedStatement insertStatement) throws SQLException;

        /**
         * <p>Gets the textual presentation of the date for the current row from the specified result set.</p>
         * 
         * @param selectedData a a <code>ResultSet</code> providing the selected data.
         * @return a <code>String</code> providing the textual presentation of the inserting record.
         * @throws SQLException if an SQL error occurs while retrieving data for specified record.
         */
        String printData(ResultSet selectedData) throws SQLException;
    }
    
    private void setLastLoadDate() throws Exception {
        PreparedStatement psUpd = null;
        StringBuffer query;

        try {
            int retVal;
            query = new StringBuffer(100);
            query.append("INSERT INTO update_log ");
            query.append("      (log_id ");        // 1
            query.append("       ,calendar_id ");  // 2
            query.append("       ,timestamp ");   // 3
            query.append("       ,log_type_id) ");   // 4
            query.append("VALUES (0, ?, ?, ").append(JIRA_LOG_TYPE).append(")");
            psUpd = prepareStatement(query.toString(), targetConn);

            int calendar_id = lookupCalendarId(fStartTime);
            psUpd.setInt(1, calendar_id);
            psUpd.setTimestamp(2, fStartTime);

            retVal = psUpd.executeUpdate();
            if (retVal != 1) {
                throw new SQLException("SetLastLoadDate " +
                        " modified " + retVal + " rows, not one.");
            }
        } catch (SQLException sqle) {
            DBMS.printSqlException(true, sqle);
            throw new Exception("Failed to set last load date.\n" +
                    sqle.getMessage());
        } finally {
            //close(psUpd);
        	psUpd.close();
        }
    }
    
    /**
     * Call this method to lookup a calendar_id from the calendar table
     * based on the Timestamp passed in. 
     */
    private int lookupCalendarId(java.sql.Timestamp date)
            throws SQLException {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        StringBuffer query = null;

        int year = cal.get(Calendar.YEAR);

        // The month is based on 0 for January so we need to add 1 to get
        // the right lookup value
        int month_of_year = cal.get(Calendar.MONTH) + 1;

        int day_of_month = cal.get(Calendar.DAY_OF_MONTH);

        PreparedStatement psSel = null;
        ResultSet rs = null;

        query = new StringBuffer(100);
        query.append("SELECT calendar_id ");
        query.append("  FROM topcoder_dw:calendar");
        query.append(" WHERE year = ? ");
        query.append("   AND month_numeric = ? ");
        query.append("   AND day_of_month = ? ");
        log.info(query.toString());
        psSel = prepareStatement(query.toString(), targetConn);

        psSel.setInt(1, year);
        psSel.setInt(2, month_of_year);
        psSel.setInt(3, day_of_month);

        try {
            rs = psSel.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                throw new SQLException("Unable to locate calendar_id for " +
                        date.toString());
            }
        } catch (SQLException sqle) {
            throw sqle;
        } finally {
            rs.close();
            psSel.close();
        }
    }
    
    /**
     * <p>A SQL statement initializer for <code>tcs_dw:jira_issue</code> database table.</p>
     */
    private static class JiraDataStatementInitializer implements InsertStatementInitializer {

        /**
         * <p>Constructs new <code>JiraDataStatementInitializer</code> instance. This implementation does nothing.</p>
         */
        private JiraDataStatementInitializer() {
        }

        /**
         * <p>Initializes the parameters for specified insert statement with data taken from the current row of 
         * specified result set.</p>
         *
         * @param selectedData    a <code>ResultSet</code> providing the selected data.
         * @param insertStatement a <code>PreparedStatement</code> providing the SQL statement for inserting record.
         * @throws SQLException if an SQL error occurs while retrieving data for specified record.
         */
        public void initialize(ResultSet selectedData, PreparedStatement insertStatement) throws SQLException {
        	
			try {
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss", Locale.US);

				String ticketId = selectedData.getString("ticket_id");
				String reporter = selectedData.getString("reporter");
				String assignee = selectedData.getString("assignee");
				String summary = selectedData.getString("summary");
				String description = selectedData.getString("description");

				Date created = new Date(dateFormat.parse(selectedData.getTimestamp("created").toString()).getTime());
				Date updated = new Date(dateFormat.parse(selectedData.getTimestamp("updated").toString()).getTime());
				Date duedate = new Date(dateFormat.parse(selectedData.getTimestamp("duedate").toString()).getTime());
				Date resolutiondate = new Date(dateFormat.parse(selectedData.getTimestamp("resolutiondate").toString()).getTime());

				long votes = selectedData.getLong("votes");
				String winner = selectedData.getString("winner");
				long payment_amount = selectedData.getLong("payment_amount");
				String contest_id = selectedData.getString("contest_id");

				insertStatement.setString(1, ticketId);
				insertStatement.setString(2, reporter);
				insertStatement.setString(3, summary);
				insertStatement.setString(4, summary);
				insertStatement.setString(5, description);
				insertStatement.setDate(6, created);
				insertStatement.setDate(7, updated);
				insertStatement.setDate(8, duedate);
				insertStatement.setDate(9, resolutiondate);
				insertStatement.setLong(10, votes);
				insertStatement.setString(11, winner);
				insertStatement.setLong(12, payment_amount);
				insertStatement.setString(13, contest_id);

			} catch (java.text.ParseException e) {
				log.error(e.getMessage());
				throw new SQLException(e.getMessage());
			}
        }

        /**
         * <p>Gets the textual presentation of the date for the current row from the specified result set.</p>
         *
         * @param selectedData a a <code>ResultSet</code> providing the selected data.
         * @return a <code>String</code> providing the textual presentation of the inserting record.
         * @throws SQLException if an SQL error occurs while retrieving data for specified record.
         */
        public String printData(ResultSet selectedData) throws SQLException {
        	DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss", Locale.US);
        	try {
        		StringBuilder b = new StringBuilder();
                b.append(selectedData.getString("ticket_id")).append(", ");
                b.append(selectedData.getString("reporter")).append(", ");
                b.append(selectedData.getString("assignee")).append(", ");
                b.append(selectedData.getString("summary")).append(", ");
                b.append(selectedData.getString("description")).append(", ");
                b.append(dateFormat.parse(selectedData.getTimestamp("created").toString())).append(", ");
                b.append(dateFormat.parse(selectedData.getTimestamp("updated").toString())).append(", ");
                b.append(dateFormat.parse(selectedData.getTimestamp("duedate").toString())).append(", ");
                b.append(dateFormat.parse(selectedData.getTimestamp("resolutiondate").toString())).append(", ");
                b.append(selectedData.getLong("votes")).append(", ");
                b.append(selectedData.getString("winner")).append(", ");
                b.append(selectedData.getLong("payment_amount")).append(", ");
                b.append(selectedData.getString("contest_id"));
                
                return b.toString();
        		
        	} catch (java.text.ParseException e) {
				log.error(e.getMessage());
				throw new SQLException(e.getMessage());
			}
        }
    }
}